The present invention relates to the management of tables in a relational database management system.
Relational Database Management Systems (RDBMS""s) have a well-defined concept of a xe2x80x98regularxe2x80x99 table, which contains data that is accessible to multiple applications that have the appropriate authority to access the table, whose data integrity is maintained by concurrency control enforced by table and row level locking; recoverability and the possibility to xe2x80x98undo workxe2x80x99 is ensured by logging.
Another type of table (application-temporary) has been introduced by a number of RDBMS products that has a more restricted scope. These xe2x80x98application-temporaryxe2x80x99 tables are created for the use of the application that created them only and cease to exist once the application disconnects from the database. This type of table need not use locking to ensure concurrency control since there is only one application accessing the table at once. This table type allows application writers to create applications that utilize tables that are guaranteed to be inaccessible to all other users of the database as well as having the performance benefit of not having locking to slow them.
An additional performance benefit can be had by (optionally) eliminating the logging of changes to application-temporary tables. The absence of logging means that changes made to an application-temporary table cannot be undone (rolled back) as is done with logged tables. Nonetheless, since the table is under the sole control of the application that created it, then the need to roll back changes may not be as necessary as with other tables, so an application writer may determine that the performance benefits of having application-tables not logged are of sufficient benefit to not require the rollback capabilities of this table.
Also, in order to have temporary tables be used to maximum benefit in a multi-node RDBMS, it is advantageous to be able to partition application-temporary tables across multiple nodes. This allows for larger temporary tables as well as the possibility of xe2x80x98collocationxe2x80x99 of data in the temporary tables with that of xe2x80x98regularxe2x80x99 tables (i.e., the data in the temporary table is partitioned in the same manner as the regular table). The performance of queries issued against tables that are collocated with each other can be greatly optimized.
However in order to implement partitioned not-logged application-temporary tables, there are a number of hurdles that must be overcome that ensure that changes made to the table are properly synchronized at all nodes. Since logging is usually the mechanism that ensures that changes to regular table are properly synchronized at all nodes, a new mechanism is needed to manage these application-temporary tables.
The invention herein provides a method and apparatus, described below, to implement partitioned not-logged application temporary tables in a manner that minimizes network traffic needed to coordinate these tables.
One aspect of the invention provides a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, comprising:
a plurality of nodes, comprising:
a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
a coordinator node for an application
at least one subordinate node for the application;
a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables.
Another aspect of the invention herein provides a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, and logged tables, comprising:
a plurality of nodes, including:
a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
a coordinator node for an application;
at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables;
a list of the declared temporary tables being dropped;
a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;
a transaction node list indicating identification of those nodes involved in a current transaction; and,
an application node list for indicating identification of all nodes that have participated in execution of the application; and,
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node.
Some aspects of this invention also apply to a multi-node environment for which there is only one partition for the declared temporary table and that partition is on the coordinator node.
Yet another aspect of the invention herein provides a multi-node relational database management system for managing a database having declared temporary tables associated with an application, and logged tables, comprising:
a plurality of nodes, including:
a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application, and a deferred drop list;
a coordinator node for an application;
at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
a declared temporary table manager for managing the declared temporary tables for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables for tracking the temporary tables;
a list of the declared temporary tables being dropped containing information to be sent to the catalog node to inform it of declared temporary tables being dropped so the catalog node can release the identification of the declared temporary tables being dropped or add the identification to its deferred drop list to release the identification when execution of the application ends;
a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of the declared temporary tables in synchronization;
a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in the current transaction to appropriately end a transaction as required for rollback or commit; and,
an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup;
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node for synchronization.
Optionally the coordinator node for an application is located at the catalog node; at least one subordinate node for an application can be at any node except that application""s coordinator node.
Another aspect of the invention provides a program product comprising a computer readable storage medium for storing instructions to establish a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, comprising:
a plurality of nodes, including:
program instructions for establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
program instructions for establishing a coordinator node for an application;
program instructions for establishing at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application; and,
program instructions for establishing a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables.
The program product may further include program instructions for establishing the declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables;
a list of the declared temporary tables being dropped;
a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;
a transaction node list indicating identification of those nodes involved in a current transaction; and,
an application node list for indicating identification of all nodes that have participated in execution of the application; and,
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node.
Yet another aspect of the invention provides a program product comprising a computer readable storage medium for storing instructions to establish a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, and logged tables, comprising:
a plurality of nodes, including:
a node failure list identifying nodes that have failed;
program instructions for establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
program instructions for establishing a coordinator node for an application;
program instructions for establishing at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
program instructions for establishing a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables;
a list of the declared temporary tables being dropped;
a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;
a transaction node list indicating identification of those nodes involved in a current transaction; and,
an application node list for indicating identification of all nodes that have participated in execution of the application; and,
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node.
Still another aspect of the invention provides a program product comprising a computer readable storage medium for storing instructions to establish a multi-node relational database management system for managing a database having declared temporary tables associated with an application, and logged tables, comprising:
a plurality of nodes, including:
program instructions for establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application, and a deferred drop list;
program instructions for establishing a coordinator node for an application;
program instructions for establishing at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
program instructions for establishing a declared temporary table manager for managing the declared temporary tables for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables for tracking the temporary tables;
a list of the declared temporary tables being dropped containing information to be sent to the catalog node to inform it of declared temporary tables being dropped so the catalog node can release the identification of the declared temporary tables being dropped or add the identification to its deferred drop list to release the identification when execution of the application ends;
a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of the declared temporary tables in synchronization;
a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in the current transaction to appropriately end a transaction as required for rollback or commit; and,
an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup;
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node for synchronization.
Another aspect of the invention provides, in a multi-node relational database management system a method for managing a database having declared temporary tables associated with respective applications, comprising:
a plurality of nodes, including:
using a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
associating a coordinator node for an application;
using at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application; and,
using a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables.
Still another aspect of the invention provides, in a multi-node relational database management system, a method for managing a database having declared temporary tables associated with respective applications, and logged tables, having a plurality of nodes, comprising:
using a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;
associating a coordinator node for an application;
using at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
using a declared temporary table manager for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables;
a list of the declared temporary tables being dropped;
a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;
a transaction node list indicating identification of those nodes involved in a current transaction; and,
an application node list for indicating identification of all nodes that have participated in execution of the application; and,
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node.
Yet another aspect of the invention provides, in a multi-node relational database management system, a method for managing a database having declared temporary tables associated with an application, and logged tables, comprising:
a plurality of nodes, including:
using a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application, and a deferred drop list;
using a coordinator node for an application;
using at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application;
using a declared temporary table manager for managing the declared temporary tables for causing:
a) the coordinator node to maintain for the application:
a list of the declared temporary tables for tracking the temporary tables;
a list of the declared temporary tables being dropped containing information to be sent to the catalog node to inform it of declared temporary tables being dropped so the catalog node can release the identification of the declared temporary tables being dropped or add the identification to its deferred drop list to release the identification when execution of the application ends;
a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of the declared temporary tables in synchronization;
a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in the current transaction to appropriately end a transaction as required for rollback or commit; and,
an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific termination processing including cleanup;
b) the subordinate node for the application to maintain a list of the declared temporary tables that are partitioned at the subordinate node for synchronization.
Preliminary Terminology
As in other fields of technology it is possible that different terminology is used to describe the same concept or structure. In this specification terminology from IBM database products such as the DB2 relational database management system will be used for the purpose of consistency.
Declared temporary table. In this specification an application-temporary table is called a xe2x80x98declared temporary tablexe2x80x99.
Coordinator node and subordinate nodes. In a multi-node system an application is connected to a database at a specific node; that node is called the coordinator node; all other nodes in the system that have some activity on them on behalf of that application are known as subordinate nodes. At the coordinator node, the RDBMS engine dispatches an xe2x80x98agentxe2x80x99 to work specifically for the application. This agent is known as the coordinator agent. Other agents working on behalf of that application, whether it be on the same node as the coordinator agent or on the subordinate nodes are known as subordinate agents.
The catalog node is the one node of the database in which system catalogs are maintained. In a well known RDBMS if the catalog node fails, the operation of the database is stopped on all nodes.
In order for the RDBMS to distinguish between different tables, each table is identified by a unique identifier, or id. If the table is partitioned on multiple nodes, then the same identifier is used to identify the table on each of nodes of the partition. Once a table has been dropped, the identifier used for that table is available to be used for other tables that are created thereafter. The identifier may be a tablespace id, file id combination.
A transaction or unit of work, is a grouping of statements issued by an application that is meant to be processed in a consistent unit. That is an application may commit a transaction, meaning that all changes made in the transaction are made xe2x80x98permanentxe2x80x99 in the database; a rollback of the transaction undoes all the changes made in the transaction.
Defined Behaviour for not Logged Declared Temporary Tables
If a declared temporary is to be not logged, then the behaviour of these tables whenever a rollback is issued is inherently different that that of xe2x80x98regularxe2x80x99 tables, and requires specific definition:
if a declared temporary table was created and a rollback of the transaction occurs, the declared temporary table is dropped. This is the same behaviour as with regular tables.
if a declared temporary table was dropped in a transaction and a rollback occurs, the declared temporary table will exist, but will be empty of all rows.
if a declared temporary table was not modified in a transaction and the transaction is rolled back, then the table remains as is, with all data therein intact. This is the same behaviour as with regular tables.
if an attempt to modify (via an insert/update/delete) the contents of a declared temporary table is made in the context of a transaction and the transaction is rolled back, then all rows of that declared temporary table (on all partitions) will be deleted. This rollback behaviour for NOT LOGGED tables is chosen so that a rollback will not leave a table in a confusing state in which it is populated with both rows from a previously committed transaction as well as rows from the transaction that was rolled back. Knowing that all rows are deleted, allows the application to handle this table in a consistent and straightforward manner by starting with the table as an empty table.
Another possible behaviour for rollback when the table has been modified is to allow all rows to remain in the table. This behaviour is not examined in this disclosure and is not supported in the current implementation.
Furthermore if a declared temporary table is not logged, the RDBMS needs to be able to handle a node failure in the context of declared temporary tables. If one node in the RDBMS fails and if a declared temporary table is partitioned on that node, then the table partition on that node will no longer exist, even if the node is restarted. In such a situation, the applications that have declared temporary tables on them need to become aware of this situation, otherwise they may assume they are still dealing with a complete partitioned table. The defined behaviour in this situation is the any attempt to access a declared temporary that had a partition on the node that failed will result in a specific error indicating that the table is not available for use.